package project7.Storage;

import java.sql.*;
import java.util.*;


/**
 * This is the main interface for interacting with the database throughout project7
 *
 * NOTES:
 *      1. LocalStorage Object set as Static as it creates a TreeMap
 *      of all the menuItems. If not static, we risk creating the TreeMap
 *      several times.
 */
public class StorageController  {

    private static StorageController instance = null;
    private DBConnect db = new DBConnect();
    private static LocalStorage localDB = null;

    private StorageController() {
        localDB = LocalStorage.getInstance();

        if (localDB.isEmpty()) {
            db.connectToDatabase("urhere.boldlygoingnowhere.org/COP", "labrat", "m1k3");
            db.executeQuery("SELECT * FROM MenuItem", Boolean.TRUE);
            localDB.loadMenuItems(db.getResultSet());
            db.closeConnections();
        }
    }

    /**
     * Use this to create a new storage controller. Doing so ensures only one database connection in open at a time.
     * @return StorageController reference to the current instance of the StorageController
     */
    public static StorageController getInstance() {
        if (instance == null) {
            instance = new StorageController();
        }

        return instance;
    }

    /**
     *
     * @param itemName
     * @return
     */
    public MenuItem getMenuItem(String itemName) {
        return localDB.getMenuItem(itemName);
    }

    /**
     *
     * @return
     */
    public ArrayList<MenuItem> getMenuItems(){
        return localDB.getMenuItems();
    }

    /**
     * This will retrieve a server from the database according to the id given.
     * @param id int of the server to find.
     * @return Server reference if server is found, null otherwise
     */
    public Server getServer(int id) {
        System.out.println("In getServer");
        Server server = null;
        db.defaultConnect();
        db.executeQuery("SELECT * FROM Server WHERE id=" + id + "", true);
        ResultSet rs = db.getResultSet();
        try {
            rs.next();
            server = new Server(rs.getInt("id"), rs.getString("last_name"), rs.getString("first_name"));
        } catch (SQLException e) {
            return null;
        }
        db.closeConnections();

        ArrayList<Table> tables = this.getOpenTables(server);
        server.setOpenTables(tables);
        server.setNumberOpenTables(tables.size());
        System.out.println("OUT getServer");
        return server;
    }

    /**
     *
     * @param number
     * @return
     */
    public Check getCheck(int number) {
        Check temp = null;
        db.defaultConnect();
        db.executeQuery("SELECT T.number AS table_number, T.check_number, T.server_id, C.total FROM Tables T, Checks C WHERE T.check_number = C.number AND T.check_number = " + number + "", true);
        ResultSet rs = db.getResultSet();
        try {
            rs.next();
            temp = new Check(rs.getInt("check_number"), rs.getInt("server_id"), getItemsPerCheck(number), rs.getFloat("total"), rs.getInt("table_number"));
            db.closeConnections();
        } catch (SQLException e) {
        }
        return temp;
    }

    /**
     *
     * This returns a Table in the database given by number; if the Table is not in the database, this returns null.
     * @param number int of the table to get
     * @param is_open set this boolean for true = open, false = close
     * @return Table reference of the table, null of it is not found.
     */
    public Table getTable(int number, boolean is_open) {
        Table temp = null;
        int i = (is_open==true?1:0);
        db.defaultConnect();
        db.executeQuery("SELECT * FROM Tables WHERE number=" + number + " AND is_open=" + i +"" , true);
        ResultSet rs = db.getResultSet();

        try {
            if(rs==null){
                System.out.println("Table Not Found");
                return null;
            }
            rs.next();

            temp = new Table(number, getServer(rs.getInt("server_id")), getCheck(rs.getInt("check_number")));
            db.closeConnections();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }
        return temp;
    }

    /**
     *
     * @param checkNumber
     * @return
     */
    public ArrayList<MenuItem> getItemsPerCheck(int checkNumber) {
        ArrayList<MenuItem> arr = new ArrayList<MenuItem>();

        db.defaultConnect();
        db.executeQuery("SELECT I.menu_item_name, I.menu_item_price, M.category FROM ItemsOrdered I, MenuItem M WHERE I.check_number=" + checkNumber + " AND I.menu_item_name=M.name", true);
        ResultSet rs = db.getResultSet();

        try {
            while (rs.next()) {
                arr.add(new MenuItem(rs.getString("menu_item_name"), rs.getFloat("menu_item_price"), rs.getString("category")));
            }
            rs.close();
            db.closeConnections();
            return arr;
        } catch (SQLException e) {
            return null;
        }

    }

    /**
     *
     * @param checkNumber
     * @param item
     * @param count
     * @return
     */
    public boolean addItemOrder(int checkNumber, MenuItem item, int count) {
        boolean temp = false;

        db.defaultConnect();
        for (int i = 0; i < count; i++) {
            temp = db.executeQuery("INSERT INTO ItemsOrdered SET check_number=" + checkNumber + ", menu_item_name='" + item.getName() + "', menu_item_price ='" + item.getPrice() + "'", false);
        }
        db.closeConnections();
        return temp;
    }

    /**
     *
     * @param checkNumber
     * @param arr
     * @return
     */
    public boolean addOrderArray(int checkNumber, ArrayList<MenuItem> arr) {
        boolean temp = false;
        db.defaultConnect();
        for (MenuItem M : arr) {
            temp = db.executeQuery("INSERT INTO ItemsOrdered SET check_number=" + checkNumber + ", menu_item_name='" + M.getName() + "', menu_item_price ='" + M.getPrice() + "'", false);
        }

        db.closeConnections();
        return temp;
    }

    /**
     *
     * @param checkNumber
     * @param itemName
     * @param count
     * @return
     */
    public boolean removeOrderItem(int checkNumber, String itemName, int count) {
        boolean temp = false;
        db.defaultConnect();
        temp = db.executeQuery("DELETE FROM ItemsOrdered WHERE menu_item_name ='" + itemName + "' AND check_number=" + checkNumber + " LIMIT " + count + "", false);
        db.closeConnections();
        return temp;
    }

    /**
     *
     * @param serverCode
     * @param arr
     * @param tableNumber
     * @param total
     * @return
     */
    public boolean addCheck(int serverCode, ArrayList<MenuItem> arr, int tableNumber, double total) {
        boolean temp = false;
        int checkNumber = -1;
        db.defaultConnect();

        /* Add check to database */
        temp = db.executeQuery("INSERT INTO Checks SET server_id=" + serverCode + ", total=" + total + "", false);

        /* ========== Get Check Number From AUTO_INCREMENT ================== */

        db.executeQuery("SELECT MAX(number) AS check_number FROM Checks WHERE server_id =" + serverCode + "", true);
        ResultSet rs = db.getResultSet();

        try {
            if (rs.next()) {
                checkNumber = rs.getInt("check_number");
            }
        } catch (SQLException e) {
        }
        /* ================================================================== */

        /* Add Items Ordered */
        addOrderArray(checkNumber, arr);

        db.closeConnections();  // Curious - must close connection after having
                                // returned a ResultSet once.

// ================== Create Table if not Already Present ======================
        

        db.defaultConnect();
        db.executeQuery("SELECT COUNT(*) AS count FROM Tables WHERE number = " + tableNumber + "", true);

        rs = db.getResultSet();
        System.out.println(db.getErrorMessage());

        try{

        rs.next();

        if(rs.getInt("count") == 0)
            addTable(serverCode,checkNumber,tableNumber);

        }catch (SQLException e) {}


        /* Close Connection */
        db.closeConnections();

        return temp;
    }

    /**
     *
     * @param serverCode
     * @param checkNumber
     * @param tableNumber
     * @return
     */
    public boolean addTable(int serverCode, int checkNumber, int tableNumber ){
        boolean temp = false;
        db.defaultConnect();
        temp = db.executeQuery("INSERT INTO Tables SET number=" + tableNumber + ", check_number=" + checkNumber + ", server_id=" + serverCode + "", false);
        db.closeConnections();
        return temp;
    }

    public boolean setTableStatus(int tableNumber, int serverCode, int checkNumber, int status){
        boolean temp = false;
        db.defaultConnect();
        temp = db.executeQuery("UPDATE Tables SET is_open=" + status + " WHERE number=" + tableNumber + ", server_id=" + serverCode + ", check_number=" + checkNumber + "", false);
        db.closeConnections();
        return temp;
    }

    public boolean getTableStatus(int tableNumber, int serverCode, int checkNumber){
        boolean temp = false;
        db.defaultConnect();
        db.executeQuery("SELECT is_open FROM Tables WHERE check_number=" + checkNumber + ", number=" + tableNumber + ", server_id=" + serverCode + "", true);

        try{
            ResultSet rs = db.getResultSet();
            rs.next();
            if(rs.getInt("is_Open")==1)
                temp = true;
        } catch (SQLException e){}

        db.closeConnections();
        return temp;
    }

    public boolean checkCode(int serverPass){
        boolean temp = false;
        db.defaultConnect();
        db.executeQuery("SELECT * FROM Server WHERE pass=" + serverPass + "", true);
        try{
            ResultSet rs = db.getResultSet();
            rs.next();
            if(rs.getInt("pass")==serverPass)
                temp = true;
        }catch (SQLException e){}
        db.closeConnections();
        return temp;
    }

    public ArrayList<Table> getAllOpenTables(int serverCode){
        System.out.println("IN getOpenTables");
        ArrayList<Table> tables = null;
        db.defaultConnect();
        db.executeQuery("SELECT * FROM Tables WHERE is_open = 1" , true);
        ResultSet rs = db.getResultSet();

        try{
            while(rs.next()){
                tables.add(new Table(rs.getInt("number"),getServer(serverCode), getCheck(rs.getInt("check_number"))));
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
        db.closeConnections();
        System.out.println("OUT getOpenTables");
        return tables;
    }

    public ArrayList<Table> getOpenTables(Server server){
        System.out.println("IN getOpenTables");
        ArrayList<Table> tables = new ArrayList<Table>();
        db.defaultConnect();
        db.executeQuery("SELECT * FROM Tables WHERE server_id=" + server.getCode() + " AND is_open=1" , true);
        ResultSet rs = db.getResultSet();

        try{
            while(rs.next()){
                System.out.println("In Loop");
                System.out.println("Incoming number = " + rs.getInt("number"));
                System.out.println("Incoming server = " + server);
                System.out.println("income check number = " + rs.getInt("check_number"));
                tables.add(new Table(rs.getInt("number"), server, getCheck(rs.getInt("check_number"))));
            }
        }catch (SQLException e){
            System.out.println(e.getMessage());
        }
        db.closeConnections();
        System.out.println("OUT getOpenTables");
        return tables;
    }


}
